const sqlite3 = require('sqlite3').verbose()
import path from "path";
const fs = require('fs')
import { SM4 } from 'gm-crypto';

const SqliteDB = function (databaseName, dbScripts) {
    try {
        console.log('db file: ', databaseName)
        let exist = fs.existsSync(databaseName);
        if (!exist) {
            console.log('creating db file')
            fs.mkdirSync(path.dirname(databaseName), { recursive: true })
            fs.writeFileSync(databaseName, '')
        }
        this.db = new sqlite3.Database(databaseName, (info)=> {
            console.log('sqlite db link info: ', info)
        })
        this.db.serialize(()=> {
            if (dbScripts) {
                dbScripts.forEach(script => {
                    this.db.run(script)
                })
            }
        })
    } catch (e) {
        console.log('e')
    }
    return this
}

SqliteDB.prototype = {
    constructor: SqliteDB,
    db: null,
    _key: 'bed51fcdf37a4941999669fd20b3ba7b',

    createTable(tableName, columns) {
        const columnDefinitions = columns.map(column => `${column.name} ${column.type}`).join(', ');
        const query = `CREATE TABLE IF NOT EXISTS ${tableName} (${columnDefinitions})`;
        this.db.run(query);
    },

    encodeVal(valStr) {
        if (typeof valStr !== 'string') {
            return valStr
        }
        let encode = SM4.encrypt(valStr, this._key, {
            inputEncoding: 'utf8',
            outputEncoding: 'hex'
        });
        console.log('encode: ', valStr, encode)
        return encode;
    },

    decodeVal(dataStr) {
        if (typeof dataStr !== 'string') {
            return dataStr
        }
        return SM4.decrypt(dataStr, this._key, {
            inputEncoding: 'hex',
            outputEncoding: 'utf8'
        })
    },

    encodeValArr(values) {
        let newValArr = []
        values.forEach(val => {
            if (typeof val === 'string') {
                val = this.encodeVal(val)
            }
            newValArr.push(val)
        })
        return newValArr
    },

    insertData(tableName, data) {
        const columns = Object.keys(data).join(', ');
        const placeholders = Object.keys(data).map(() => '?').join(', ');
        const values = this.encodeValArr(Object.values(data));
        const query = `INSERT INTO ${tableName} (${columns}) VALUES (${placeholders})`;
        console.log('execute insert sql: ', query, values);
        this.db.run(query, values);
    },

    updateData(tableName, data, condition) {
        console.log('update info: ', data, condition)
        const setClause = Object.keys(data).map(column => `${column} = ?`).join(', ');
        let values = this.encodeValArr(Object.values(data));

        const conditionFormat = this._formatQueryCondition(condition)
        const where = conditionFormat.condition === '' ? '' : ` WHERE ${conditionFormat.condition} `

        const query = `UPDATE ${tableName} SET ${setClause} ${where}`;
        console.log('execute update sql: ', query, [...values, ...conditionFormat.values]);
        this.db.run(query, [...values, ...conditionFormat.values]);
    },

    deleteData(tableName, condition) {
        const conditionFormat = this._formatQueryCondition(condition)
        const where = conditionFormat.condition === '' ? '' : ` WHERE ${conditionFormat.condition} `
        const query = `DELETE FROM ${tableName} ${where}`;
        this.db.run(query, conditionFormat.values);
    },

    executeQuery(query, params, callback) {
        this.db.all(query, params, (err, rows) => {
            if (err) {
                console.error(err);
            } else {
                callback(rows);
            }
        });
    },

    /**
     * 获取分页数据
     * @param tableName
     * @param page
     * @param pageSize
     * @param condition {string | object}
     * @returns {Promise<unknown>}
     */
    getPagedData(tableName, page, pageSize,condition) {
        const offset = (page - 1) * pageSize;
        const conditionFormat = this._formatQueryCondition(condition)
        const where = conditionFormat.condition === '' ? '' : ` WHERE ${conditionFormat.condition} `
        const countQuery = `SELECT COUNT(*) as total FROM ${tableName} ${where}`;
        const dataQuery = `SELECT * FROM ${tableName} ${where} LIMIT ? OFFSET ?`;
        console.log('execute page query sql: ', countQuery, dataQuery, page, pageSize)

        return new Promise((resolve, reject) => {
            this.db.serialize(() => {
                this.db.get(countQuery, conditionFormat.values, (err, row) => {
                    if (err) {
                        reject(err);
                    } else {
                        const total = row.total;

                        this.db.all(dataQuery, [...conditionFormat.values, ...[pageSize, offset]], (err, rows) => {
                            if (err) {
                                reject(err);
                            } else {
                                const totalPages = Math.ceil(total / pageSize);

                                resolve({ data: rows, total, totalPages });
                            }
                        });
                    }
                });
            });
        });
    },

    _formatQueryCondition(condition) {
        let values = []
        let conditionStr = ''
        if (null == condition) {
            condition = ''
        }
        if (typeof condition === 'string') {
            conditionStr = condition;
        } else {
            conditionStr = Object.keys(condition).map(column => {
                if (typeof condition[column] === 'string') {
                    values.push(condition[column])
                    return `${column} = ?`
                } else {
                    const valObject = condition[column]
                    if (valObject.type === 'in') {
                        const placeholders = Object.keys(valObject.value).map(() => '?').join(',')
                        values = [...values, ...valObject.value]
                        return `${column} in (${placeholders})`
                    } else if (valObject.type === 'between') {
                        values = [...values, ...valObject.value]
                        return `${column} between ? and ?`
                    } else if (valObject.type === 'like') {
                        values.push(valObject.value)
                        return `${column} like ?`
                    } else {
                        values.push(valObject)
                        return `${column} = ?`
                    }
                }
            }).join(' and ');
            values = this.encodeValArr(values);
        }

        return {
            condition: conditionStr,
            values: values
        }
    },

    getAllData(tableName, condition) {
        const conditionFormat = this._formatQueryCondition(condition)
        const conditionStr = conditionFormat.condition
        const values = conditionFormat.values

        const where = conditionStr === '' ? '' : ` WHERE ${conditionStr} `
        const dataQuery = `SELECT * FROM ${tableName} ${where}`
        console.log('execute get all data sql: ', dataQuery, values)
        return new Promise((resolve, reject) => {
            this.db.serialize(() => {
                this.db.all(dataQuery, values, (err, rows) => {
                    if (err) {
                        reject(err);
                    } else {
                        resolve(rows);
                    }
                });
            })
        })
    },

    closeConnection() {
        this.db.close();
    }
}

export { SqliteDB }